package util;


import lombok.Data;
import lombok.NonNull;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 导出
 *
 * @author wky
 * @date 2019/8/12
 */
@Slf4j
@Data
public class ExcelUtil {
    /**
     * 表头
     */
    List<String> header;

    /**
     * 映射数据
     */
    List<String> mappingData;

    /**
     * 数据
     */
    List<Map<String, Object>> data;

    private final int pageSize = 5000;

    public ExcelUtil(List<String> header, List<String> mappingData, List<Map<String, Object>> data) {
        this.header = header;
        this.mappingData = mappingData;
        this.data = data;
    }

    public ExcelUtil() {
    }

    int getTotal(int size) {
        int index = size / pageSize;
        if (size % pageSize != 0) {
            index++;
        }
        return index;
    }


    public boolean export(@NonNull OutputStream outputStream) throws IOException {
        if (header == null || header.isEmpty()) {
            log.info("导出参数错误，header为空");
            return false;
        }

        int total = getTotal(data.size());

        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
        List<SXSSFSheet> sheetList = new ArrayList<>(total);

        // 设置并获取到需要的样式
        XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(sxssfWorkbook);
        XSSFCellStyle xssfCellStyleOne = getAndSetXSSFCellStyle(sxssfWorkbook);
        // 前景颜色
        xssfCellStyleOne.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        xssfCellStyleOne.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        XSSFCellStyle xssfCellStyleTwo = getAndSetXSSFCellStyle(sxssfWorkbook);

        // 初始化每个sheet 当没有数据也初始化表头
        for (int i = 0; total == 0 || i < total; i++) {
            SXSSFSheet sheet = sxssfWorkbook.createSheet();
            // 冻结最左边的两列、冻结最上面的一行
            // 即：滚动横向滚动条时，左边的第一、二列固定不动;滚动纵向滚动条时，上面的第一行固定不动。
            sheet.createFreezePane(2, 1);
            // 创建第一行,作为header表头
            Row head = sheet.createRow(0);
            // 循环创建header单元格(根据实际情况灵活创建即可)
            for (int cellnum = 0; cellnum < header.size(); cellnum++) {
                Cell cell = head.createCell(cellnum);
                cell.setCellStyle(xssfCellStyleHeader);
                cell.setCellValue(header.get(cellnum));
                if (cell.getCellType() == CellType.STRING) {
                    sheet.setColumnWidth(cellnum, sheet.getColumnWidth(cellnum) * 17 / 10);
                }
            }
            sheetList.add(sheet);
            if (total == 0) {
                break;
            }
        }

        // 遍历数据
        if (mappingData != null && mappingData.size() > 0) {
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            for (int rownum = 1; rownum <= data.size(); rownum++) {
                Row row = sheetList.get((rownum - 1) / pageSize).createRow(((rownum - 1) % pageSize) + 1);
                Map<String, Object> map = data.get(rownum - 1);
                // 循环创建单元格
                for (int cellnum = 0; cellnum < mappingData.size(); cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    // 根据行数,设置该行内的单元格样式
                    if (rownum % 2 == 1) {
                        // 奇数
                        cell.setCellStyle(xssfCellStyleOne);
                    } else {
                        // 偶数
                        cell.setCellStyle(xssfCellStyleTwo);
                    }
                    String key = mappingData.get(cellnum);
                    Object value = map.get(key);
                    String cellValue = "-";
                    if (value != null) {
                        if (value instanceof Date) {
                            cellValue = formatter.format(value);
                        } else {
                            cellValue = value.toString();
                        }
                    }
                    cell.setCellValue(cellValue);
                }
            }
        }
        sxssfWorkbook.write(outputStream);
        return true;
    }

    /**
     * 获取并设置header样式
     */
    private static XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        Font font = sxssfWorkbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 14);
        // 字体粗细
        font.setBold(true);
        // 将字体应用到样式上面
        xssfCellStyle.setFont(font);
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return xssfCellStyle;
    }

    /**
     * 获取并设置样式
     */
    private XSSFCellStyle getAndSetXSSFCellStyle(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        XSSFDataFormat format = (XSSFDataFormat) sxssfWorkbook.createDataFormat();
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN);
        xssfCellStyle.setBorderRight(BorderStyle.THIN);
        xssfCellStyle.setBorderTop(BorderStyle.THIN);
        xssfCellStyle.setBorderLeft(BorderStyle.THIN);
        xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
        xssfCellStyle.setDataFormat(format.getFormat("0"));
        return xssfCellStyle;
    }

}
